package com.regulareport.common.utils.poi;

import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.databind.SerializationFeature;
import com.fasterxml.jackson.databind.node.ArrayNode;
import com.fasterxml.jackson.databind.node.ObjectNode;
import com.regulareport.common.exception.ServiceException;
import com.regulareport.common.utils.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.ss.util.PaneInformation;

import java.io.ByteArrayInputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.nio.file.Files;
import java.nio.file.Path;
import java.text.DecimalFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * @author shkstart
 * @create 2025-09-05 14:23
 */
public class ExcelToJsonConverter3 {

    private static final ObjectMapper objectMapper = new ObjectMapper();

    /**
     * 前端支持的对齐格式
     */
    private static final String[] alignArr = {"CENTER", "LEFT", "RIGHT"};

    /**
     * 前端支持的对齐格式
     */
    private static final String[] valignArr = {"TOP", "CENTER", "BOTTOM"};

    /**
     * 边框不存在 “NONE”
     */
    private static final String borderNo = "NONE";

    /**
     * 正则表达式模式：匹配数字和点组成的编号，然后是名称
     */
    private static final Pattern pattern = Pattern.compile(".*?[:：]?([\\d.]+[a-z]?|[\\u2160-\\u216F]+)[\\s.]*(.*)?$");

    /**
     * 转换保存二进制转化为InputStream
     */
//    public static String convertByteArrayWithInputStream(byte[] fileJson) throws Exception {
//        InputStream input = new ByteArrayInputStream(fileJson);
//        String fileData = "";
//        try {
//            fileData = convertExcelWithStyles(input, false, false, false);
//            input.close();
//        } catch (Exception e) {
//            throw new RuntimeException(e);
//        } finally {
//            try {
//                input.close();
//            } catch (IOException ex) {
//                throw new RuntimeException(ex);
//            }
//        }
//        return fileData;
//    }

    /**
     * 转换Excel为包含合并单元格和样式的JSON
     */
    public static String convertExcelWithStyles(Path path, boolean alwaysLock, boolean isCompress, boolean evaluate) throws Exception {

        try ( InputStream inputStream = Files.newInputStream(path);
                Workbook workbook = new HSSFWorkbook(inputStream)){
            ObjectNode result = objectMapper.createObjectNode();
//                int sheetTotal = workbook.getNumberOfSheets();
//                for(int i = 0; i <workbook.getNumberOfSheets(); i++) {
            Sheet sheet = workbook.getSheetAt(0);
            FormulaEvaluator evaluator = null;
            if (evaluate) {
                evaluator = workbook.getCreationHelper().createFormulaEvaluator();
            }

            ObjectNode sheetData = convertSheetWithFullStyles(sheet, alwaysLock, evaluator, evaluate);
//                   result.set("sheet" + i, sheetData);
            result.set("sheet1", sheetData);
//                }

            if (isCompress) {
                objectMapper.disable(SerializationFeature.INDENT_OUTPUT);
                return objectMapper.writeValueAsString(result);
            } else {
                return objectMapper.writerWithDefaultPrettyPrinter().writeValueAsString(result);
            }
        } catch (Exception e) {
            throw e;
        }
    }

    /**
     * 转换单个工作表（完整样式支持）
     */
    private static ObjectNode convertSheetWithFullStyles(Sheet sheet, boolean alwaysLock, FormulaEvaluator evaluator, boolean evaluate) {
        ObjectNode sheetNode = objectMapper.createObjectNode();
        sheetNode.put("name", sheet.getSheetName());

        // 处理合并单元格
        List<CellRangeAddress> mergedRegions = sheet.getMergedRegions();
        ArrayNode mergeCellsArray = objectMapper.createArrayNode();
        ArrayNode mergeArray = objectMapper.createArrayNode();
        Set styleSet = new LinkedHashSet();
        for (CellRangeAddress mergedRegion : mergedRegions) {
            ObjectNode mergeNode = objectMapper.createObjectNode();
            mergeNode.put("range", mergedRegion.formatAsString());
            mergeNode.put("firstRow", mergedRegion.getFirstRow());
            mergeNode.put("lastRow", mergedRegion.getLastRow());
            mergeNode.put("firstColumn", mergedRegion.getFirstColumn());
            mergeNode.put("lastColumn", mergedRegion.getLastColumn());

            // 获取主单元格的值和样式
            Row firstRow = sheet.getRow(mergedRegion.getFirstRow());
            if (firstRow != null) {
                Cell firstCell = firstRow.getCell(mergedRegion.getFirstColumn());

                if (firstCell != null) {
                    mergeNode.put("text", getCellValueAsString(firstCell, evaluator, evaluate));
                    mergeNode.set("style", extractCellStyle(firstCell));
                }
            }
            mergeArray.add(mergedRegion.formatAsString());
            mergeCellsArray.add(mergeNode);
        }
        //获取冻结行列cell
        PaneInformation paneInfo = sheet.getPaneInformation();
        String a1Address = "";
        if (paneInfo != null) {
            System.out.println("paneInfo是否冻结: " + paneInfo.isFreezePane());
            int firstActiveCol = paneInfo.getVerticalSplitLeftColumn();
            int firstActiveRow = paneInfo.getHorizontalSplitTopRow();
            short colIndex = paneInfo.getVerticalSplitPosition();
            short rowIndex = paneInfo.getHorizontalSplitPosition();
            System.out.println("firstActiveCol===========" + firstActiveCol);
            System.out.println("firstActiveRow===========" + firstActiveRow);
            System.out.println("colIndex===========" + colIndex);
            System.out.println("rowIndex===========" + rowIndex);
            a1Address = new CellReference(rowIndex, colIndex).formatAsString();
            String a1Address1 = new CellReference(firstActiveRow, firstActiveCol).formatAsString();
            System.out.println("活动冻结区域: " + a1Address);
            System.out.println("活动区域首单元格: " + a1Address1);
            sheetNode.put("freeze", a1Address);
        }
//            sheetNode.set("mergeCells", mergeCellsArray);

        // 创建合并单元格映射，用于后续检查
        Map<String, CellRangeAddress> mergeMap = new HashMap<>();
        for (CellRangeAddress mergedRegion : mergedRegions) {
            for (int row = mergedRegion.getFirstRow(); row <= mergedRegion.getLastRow(); row++) {
                for (int col = mergedRegion.getFirstColumn(); col <= mergedRegion.getLastColumn(); col++) {
                    String key = row + "," + col;
                    mergeMap.put(key, mergedRegion);
                }
            }
        }

        // 处理所有单元格数据
        ObjectNode sheetRowsNode = objectMapper.createObjectNode();
        ObjectNode colWidthNode = objectMapper.createObjectNode();
        int lastRowNum = sheet.getLastRowNum();
        int maxCellNum = 0;

        for (int rowNum = 0; rowNum <= lastRowNum; rowNum++) {

            Row row = sheet.getRow(rowNum);
            if (row == null) {
                // 处理空行
                ObjectNode emptyRowNode = objectMapper.createObjectNode();
                emptyRowNode.put("isEmpty", true);
                sheetRowsNode.set(String.valueOf(rowNum), emptyRowNode);
                continue;
            }

            int rowHeight = (int) ((row.getHeightInPoints() / 72) * 96);

            ObjectNode rowNode = objectMapper.createObjectNode();
            rowNode.put("isEmpty", false);
            rowNode.put("height", rowHeight);

            ObjectNode rowCellsNode = objectMapper.createObjectNode();
            short lastCellNum = row.getLastCellNum();

            for (int colNum = 0; colNum < lastCellNum; colNum++) {
                String cellRef = new CellReference(rowNum, colNum).formatAsString();
                Cell cell = row.getCell(colNum);
                //获取列col宽
                if (rowNum == 0 && cell != null) {
                    ObjectNode colWidthObject = objectMapper.createObjectNode();
                    int colWidth = (int) sheet.getColumnWidthInPixels(cell.getColumnIndex());
                    colWidthObject.put("width", colWidth);
                    colWidthNode.set(String.valueOf(colNum), colWidthObject);
                }

                String cellKey = rowNum + "," + colNum;

                ObjectNode cellNode = objectMapper.createObjectNode();
                // 检查是否是合并单元格的一部分
                if (mergeMap.containsKey(cellKey)) {
                    CellRangeAddress mergedRegion = mergeMap.get(cellKey);
                    cellNode.put("isMerged", true);
                    cellNode.put("isMaster",
                            mergedRegion.getFirstRow() == rowNum &&
                                    mergedRegion.getFirstColumn() == colNum);
                    //通过坐标获取合并单元格首行首列值
                    HSSFRow mergeFirstRow = (HSSFRow) sheet.getRow(mergedRegion.getFirstRow());
                    Cell mergeFirstCell = mergeFirstRow.getCell(mergedRegion.getFirstColumn());
                    cellNode.put("mergeText1", getCellValueAsString(mergeFirstCell, evaluator,evaluate));

                    // 如果是主单元格，保存值和样式
                    if (cellNode.get("isMaster").asBoolean() && cell != null) {
                        cellNode.put("text", getCellValueAsString(cell, evaluator,evaluate));
                        cellNode.set("style", extractCellStyle(cell));
                        CellStyle cellStyle = cell.getCellStyle();
                        if (alwaysLock) {
                            cellNode.put("editable", false);
                        } else {
                            cellNode.put("editable", cellStyle.getLocked());
                        }
                        //merge赋值
                        ArrayNode rowCellArray = objectMapper.createArrayNode();
                        rowCellArray.add(Integer.valueOf(mergedRegion.getLastRow() - rowNum));
                        rowCellArray.add(mergedRegion.getLastColumn() - mergedRegion.getFirstColumn());
                        cellNode.set("merge", rowCellArray);
                    } else {
                        cellNode.set("style", extractCellStyle(cell));
                    }
                    //判断是否为公式并获取
                    if (cell != null && cell.getCellType() == CellType.FORMULA) {
                        String formula = cell.getCellFormula();
                        cellNode.put("formula", formula);
                    }
                    //背景色设置，指标定义需要使用
                    cellNode.set("bgColor", obtainCellStyleBgColor(cell).get("bgcolor"));
                    if(maxCellNum < colNum){
                        maxCellNum = colNum;
                    }
                } else if (cell != null) {
                    // 普通单元格
                    cellNode.put("isMerged", false);
                    cellNode.put("isMaster", false);
                    cellNode.put("text", getCellValueAsString(cell, evaluator,evaluate));
                    cellNode.set("style", extractCellStyle(cell));
                    CellStyle cellStyle = cell.getCellStyle();
                    if (alwaysLock) {
                        cellNode.put("editable", false);
                    } else {
                        cellNode.put("editable", cellStyle.getLocked());
                    }
                    //判断是否为公式并获取
                    if (cell.getCellType() == CellType.FORMULA) {
                        String formula = cell.getCellFormula();
                        cellNode.put("formula", formula);
                    }
                    cellNode.set("bgColor", obtainCellStyleBgColor(cell).get("bgcolor"));
                    if(IsExistCellStyleRightBorder(cell) || StringUtils.isNotEmpty(getCellValueAsString(cell , evaluator,  evaluate))){ //是否存在右边框或存在值
                        if(maxCellNum < colNum){
                            maxCellNum = colNum;
                        }
                    }
                    cellNode.put("editable", false);
                } else {
                    // 空单元格
                    cellNode.put("isMerged", false);
                    cellNode.put("isMaster", false);
                    cellNode.put("text", "");
                    cellNode.set("style", objectMapper.createObjectNode());
                }
                //
                if (cellNode != null && cellNode.findValue("style") != null && !cellNode.findValue("style").isEmpty()) {
                    styleSet.add(cellNode.findValue("style"));
                    cellNode.set("style", objectMapper.valueToTree(findPosition(styleSet, cellNode.findValue("style"))));
                    rowCellsNode.set(String.valueOf(colNum), cellNode);
                }

            }
            rowNode.set("cells", rowCellsNode);
//                rowNode.set("cells", cellsArray);
//                rowsArray.add(rowNode);
            sheetRowsNode.set(String.valueOf(rowNum), rowNode);
        }
        // 将LinkedHashSet转换为List
        List list = (List) styleSet.stream().collect(Collectors.toList());

        // 使用Jackson将List转换为ArrayNode
        ObjectMapper mapper = new ObjectMapper();
        ArrayNode arrayNodeTyle = mapper.valueToTree(list);
        sheetNode.set("merges", mergeArray);
        sheetNode.set("styles", arrayNodeTyle);
        sheetNode.set("rows", sheetRowsNode);
        sheetNode.set("cols", colWidthNode);
        System.out.println("报表编号=========="+sheet.getSheetName()+", 总行数rowNum==== " + lastRowNum);
        System.out.println("报表编号=========="+sheet.getSheetName()+", 最大列数cellNum==== " + maxCellNum);
        sheetNode.put("rowNum", lastRowNum+1); //总计多少行
        sheetNode.put("cellNum", maxCellNum+1); //最多多少列
        return sheetNode;
    }

    /**
     * 提取单元格样式信息
     */
    private static ObjectNode extractCellStyle(Cell cell) {
        ObjectNode styleNode = objectMapper.createObjectNode();
        if (cell == null) return styleNode;

        HSSFCellStyle cellStyle = (HSSFCellStyle) cell.getCellStyle();
        HSSFWorkbook workbook = (HSSFWorkbook) cell.getSheet().getWorkbook();
        // 背景颜色
        styleNode = obtainCellStyleBgColor(cell);
        // 字体样式
        try {
            Font font = workbook.getFontAt(cellStyle.getFontIndex());
            if (font != null) {
                ObjectNode fontNode = objectMapper.createObjectNode();
//                    fontNode.put("name", font.getFontName());
                fontNode.put("size", font.getFontHeightInPoints());
                fontNode.put("bold", font.getBold());
                fontNode.put("italic", font.getItalic());
                fontNode.put("underline", font.getUnderline());

                // 字体颜色
//                    short fontColor = font.getColor();
                styleNode.set("font", fontNode);
            }
        } catch (Exception e) {
            // 忽略字体提取错误
            e.printStackTrace();
        }
        // 边框样式
        try {
            Map borderNode = new HashMap();
            //如果四条边框同时不存在，不提供border
            if (!cellStyle.getBorderTop().toString().equals(borderNo) || !cellStyle.getBorderLeft().toString().equals(borderNo)
                    || !cellStyle.getBorderRight().toString().equals(borderNo) || !cellStyle.getBorderBottom().toString().equals(borderNo)) {
                borderNode.put("top", getBorderStyle(cellStyle.getBorderTop(), cellStyle.getTopBorderColor()));
                borderNode.put("bottom", getBorderStyle(cellStyle.getBorderBottom(), cellStyle.getBottomBorderColor()));
                borderNode.put("left", getBorderStyle(cellStyle.getBorderLeft(), cellStyle.getLeftBorderColor()));
                borderNode.put("right", getBorderStyle(cellStyle.getBorderRight(), cellStyle.getRightBorderColor()));
                styleNode.set("border", objectMapper.valueToTree(borderNode));
            }
        } catch (Exception e) {
            // 忽略边框提取错误
            throw new ServiceException(e.getMessage());
        }

        // 对齐方式
        try {
            styleNode.put("align", Arrays.asList(alignArr).contains(cellStyle.getAlignment().toString()) ? cellStyle.getAlignment().toString().toLowerCase() : "left");
            if (cellStyle.getVerticalAlignment() != null && cellStyle.getVerticalAlignment().toString().equals("CENTER")) {
                styleNode.put("valign", "middle");
            } else {
                styleNode.put("valign", Arrays.asList(valignArr).contains(cellStyle.getVerticalAlignment().toString()) ? cellStyle.getVerticalAlignment().toString().toLowerCase() : "middle");
            }
        } catch (Exception e) {
            // 忽略对齐方式提取错误
            throw new ServiceException(e.getMessage());
        }
        //内容是否换行
        styleNode.put("textwrap", cellStyle.getWrapText());

        return styleNode;
    }

    // 背景颜色
    private static ObjectNode obtainCellStyleBgColor(Cell cell) {
        ObjectNode styleNode = objectMapper.createObjectNode();
        if (cell == null) return styleNode;
        HSSFCellStyle cellStyle = (HSSFCellStyle) cell.getCellStyle();
        String customHexColor = "";
        try {
            if (cellStyle != null && cellStyle.getFillPattern() == FillPatternType.SOLID_FOREGROUND) {
                HSSFColor bgColor = cellStyle.getFillForegroundColorColor();
                if (bgColor != null) {
                    customHexColor = String.format("#%02x%02x%02x",
                            bgColor.getTriplet()[0],
                            bgColor.getTriplet()[1],
                            bgColor.getTriplet()[2]);
                }
                if (customHexColor.equals("#99ccff") || customHexColor.equals("#9bc2e6") || customHexColor.equals("#0066cc")) { //模板蓝色
                    customHexColor = "#9cc2e6";  //页面蓝色
                    cellStyle.setLocked(true);
                }
                if (customHexColor.equals("#c0c0c0") || customHexColor.equals("#bfbfbf") || customHexColor.equals("#969696")) {//模板灰色
                    customHexColor = "#d0cecf";  //页面灰色
                    cellStyle.setLocked(true);
                }
                styleNode.put("bgcolor", customHexColor);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return styleNode;
    }

    /**
     * 获取边框样式名称
     */
    private static List getBorderStyle(BorderStyle borderStyle, short borderColor) {
        List bordStyles = new ArrayList();
        if (borderStyle != null && !borderStyle.equals(borderNo)) {
            bordStyles.add(borderStyle.toString().toLowerCase() != "thin" ? "thin" : borderStyle.toString().toLowerCase());
            bordStyles.add(borderColor == 0 ? "#000" : colorConverter(borderColor));
        }

        return bordStyles;
    }

    //是否存在右边框 调用方已判断cell不为null
    private static boolean IsExistCellStyleRightBorder(Cell cell){
        HSSFCellStyle cellStyle = (HSSFCellStyle) cell.getCellStyle();
        if(cellStyle != null && !cellStyle.getBorderRight().toString().equals(borderNo)) {
            return true;
        }
        return false;
    }

    //判断单元格是否可编辑
    private static boolean judgeCellStyleEditable(Cell cell){
        boolean  editable = true ;
//        if(StringUtils.isNotEmpty(getCellValueAsString(cell, evaluator, evaluate))){
//            editable = false;
//        }
        if(obtainCellStyleBgColor(cell) != null && obtainCellStyleBgColor(cell).get("bgcolor") != null && ("#9cc2e6".equals(obtainCellStyleBgColor(cell).get("bgcolor").asText()) || "#d0cecf".equals(obtainCellStyleBgColor(cell).get("bgcolor").asText()))){
            editable = false;
        }
        if(!isCompleteCellStyleBorder(cell)) {
            editable = false;
        }
        return editable;
    }

    private static boolean  isCompleteCellStyleBorder(Cell cell){
        HSSFCellStyle cellStyle = (HSSFCellStyle) cell.getCellStyle();
        if(cellStyle != null && (cellStyle.getBorderTop().toString().equals(borderNo) || cellStyle.getBorderLeft().toString().equals(borderNo)
                || cellStyle.getBorderRight().toString().equals(borderNo) || cellStyle.getBorderBottom().toString().equals(borderNo))) {
            return false;
        }
        return true;
    }

    /**
     * 获取单元格值的字符串表示
     */
    public static String getCellValueAsString(Cell cell, FormulaEvaluator evaluator, boolean evaluate) {
        if (cell == null) return "";
        CellStyle cellStyle = cell.getCellStyle();
        short dataFormat = cellStyle.getDataFormat();
        String dataFormatString = cellStyle.getDataFormatString();
        DecimalFormat df = new DecimalFormat("#,##0.00");
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    // 避免科学计数法
                    double num = cell.getNumericCellValue();
                    BigDecimal bigDecimal = new BigDecimal(String.valueOf(num));
                    System.out.println("cell location:" + cell.getRowIndex() + "," + cell.getColumnIndex() +
                            ", dataFormatString:" + dataFormatString);
                    if (dataFormatString.contains(";")) {
                        String[] split = dataFormatString.split(";");
                        dataFormatString = split[0];
                        dataFormatString = dataFormatString.replace(")", "");
                    }
                    if ("0.00_".equals(dataFormatString.trim())) {
                        bigDecimal = bigDecimal.setScale(2, RoundingMode.HALF_UP);
                    } else if ("0_".equals(dataFormatString.trim()) || "General".equalsIgnoreCase(dataFormatString.trim())) {
                        bigDecimal = bigDecimal.setScale(0, RoundingMode.HALF_UP);
                    } else if ("0.00%".equals(dataFormatString.trim())) {
                        bigDecimal = bigDecimal.setScale(4, RoundingMode.HALF_UP);
                        BigDecimal multiply = bigDecimal.multiply(new BigDecimal("100"));
                        BigDecimal s = multiply.setScale(2);
                        return s + "%";
                    } else if ("#,##0.00_".equalsIgnoreCase(dataFormatString.trim())) {
                        bigDecimal = bigDecimal.setScale(2, RoundingMode.HALF_UP);
                        return df.format(bigDecimal);
                    }
                    return bigDecimal.toPlainString();
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                try {
                    if (evaluate) {
                        CellValue cellValue = evaluator.evaluate(cell);
                        // 6. 根据计算结果的数据类型进行处理
                        switch (cellValue.getCellType()) {
                            case NUMERIC:
                                double numberValue = cellValue.getNumberValue();
                                BigDecimal bigDecimal = new BigDecimal(String.valueOf(numberValue));
                                System.out.println("cell location:" + cell.getRowIndex() + "," + cell.getColumnIndex() +
                                        ", dataFormatString:" + dataFormatString);
                                if (dataFormatString.contains(";")) {
                                    String[] split = dataFormatString.split(";");
                                    dataFormatString = split[0];
                                    dataFormatString = dataFormatString.replace(")", "");
                                }
                                if ("0.00_".equals(dataFormatString.trim())) {
                                    bigDecimal = bigDecimal.setScale(2, RoundingMode.HALF_UP);
                                } else if ("0_".equals(dataFormatString.trim()) || "General".equalsIgnoreCase(dataFormatString.trim())) {
                                    bigDecimal = bigDecimal.setScale(0, RoundingMode.HALF_UP);
                                } else if ("0.00%".equals(dataFormatString.trim())) {
                                    bigDecimal = bigDecimal.setScale(4, RoundingMode.HALF_UP);
                                    BigDecimal multiply = bigDecimal.multiply(new BigDecimal("100"));
                                    BigDecimal s = multiply.setScale(2);
                                    return s + "%";
                                } else if ("#,##0.00_".equalsIgnoreCase(dataFormatString.trim())) {
                                    bigDecimal = bigDecimal.setScale(2, RoundingMode.HALF_UP);
                                    return df.format(bigDecimal);
                                }
                                return bigDecimal.toPlainString();
                            case STRING:
                                System.out.println("公式结果（字符串）: " + cellValue.getStringValue());

                                return String.valueOf(cellValue.getStringValue());
                            case BOOLEAN:
                                return String.valueOf(cellValue.getBooleanValue());
                            case ERROR:
                                return "零除错误";
                            default:
                                return String.valueOf(cell.getNumericCellValue());
                        }
                    } else {
                        return String.valueOf(cell.getNumericCellValue());
                    }
                } catch (Exception e) {
                    try {
                        return cell.getStringCellValue();
                    } catch (Exception ex) {
                        return cell.getCellFormula();
                    }
                }
            case BLANK:
                return "";
            default:
                return "";
        }
    }


    public static BigDecimal getCellValueAsStringOri(Cell cell, FormulaEvaluator evaluator, boolean evaluate) {
        if (cell == null) return null;
        try {
            CellStyle cellStyle = cell.getCellStyle();
            switch (cell.getCellType()) {
                case STRING:
                    BigDecimal bigDecimal = new BigDecimal(cell.getStringCellValue());
                    BigDecimal bigDecimal1 = bigDecimal.setScale(6, RoundingMode.HALF_UP);
                    return bigDecimal1;
                case NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        BigDecimal bigDecimal2 = new BigDecimal(cell.getDateCellValue().toString());
                        BigDecimal bigDecimal3 = bigDecimal2.setScale(6, RoundingMode.HALF_UP);
                        return bigDecimal3;
                    } else {
                        // 避免科学计数法
                        double num = cell.getNumericCellValue();
                        BigDecimal bigDecimal4 = new BigDecimal(String.valueOf(num));
                        BigDecimal bigDecimal5 = bigDecimal4.setScale(6, RoundingMode.HALF_UP);
                        return bigDecimal5;
                    }
                case BOOLEAN:
                    BigDecimal bigDecimal6 = new BigDecimal(String.valueOf(cell.getBooleanCellValue()));
                    BigDecimal bigDecimal7 = bigDecimal6.setScale(6, RoundingMode.HALF_UP);
                    return bigDecimal7;
                case FORMULA:
                    try {
                        if (evaluate) {
                            CellValue cellValue = evaluator.evaluate(cell);
                            // 6. 根据计算结果的数据类型进行处理
                            switch (cellValue.getCellType()) {
                                case NUMERIC:
                                    double num = cellValue.getNumberValue();
                                    bigDecimal = new BigDecimal(String.valueOf(num));
                                    bigDecimal = bigDecimal.setScale(6, RoundingMode.HALF_UP);
                                    return bigDecimal;
                                case STRING:
                                    System.out.println("公式结果（字符串）: " + cellValue.getStringValue());
                                    bigDecimal = new BigDecimal(String.valueOf(cellValue.getStringValue()));
                                    bigDecimal = bigDecimal.setScale(6, RoundingMode.HALF_UP);
                                    return bigDecimal;
                                case BOOLEAN:
                                    bigDecimal6 = new BigDecimal(String.valueOf(cell.getBooleanCellValue()));
                                    bigDecimal7 = bigDecimal6.setScale(6, RoundingMode.HALF_UP);
                                    return bigDecimal7;
                                case ERROR:
                                    return null;
                                default:
                                bigDecimal6 = new BigDecimal(cellValue.getStringValue());
                                bigDecimal7 = bigDecimal6.setScale(6, RoundingMode.HALF_UP);
                                return bigDecimal7;
                            }
                        } else {
                            bigDecimal6 = new BigDecimal(String.valueOf(cell.getNumericCellValue()));
                            bigDecimal7 = bigDecimal6.setScale(6, RoundingMode.HALF_UP);
                            return bigDecimal7;
                        }
                    } catch (Exception e) {
                        bigDecimal6 = new BigDecimal(String.valueOf(cell.getStringCellValue()));
                        bigDecimal7 = bigDecimal6.setScale(6, RoundingMode.HALF_UP);
                        return bigDecimal7;
                    }
                case BLANK:
                    return null;
                default:
                    return null;
            }
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }
//
//    private static BigDecimal safeStringChangeBigDecimal(String big) {
//        BigDecimal bigDecimal =  new BigDecimal(big);
//        bigDecimal.setScale(6, RoundingMode.HALF_UP);
//
//        return bigDecimal;
//    }
//
//    /**
//     * 获取单元格值的字符串表示
//     */
//    public static BigDecimal getCellValueAsStringOri(Cell cell, FormulaEvaluator evaluator, boolean evaluate) {
//        BigDecimal bigDecimal = new BigDecimal("0");
//        if (cell == null) return bigDecimal;
//        switch (cell.getCellType()) {
//            case STRING:
//                return safeStringChangeBigDecimal(cell.getStringCellValue());
//            case NUMERIC:
//                if (DateUtil.isCellDateFormatted(cell)) {
//                    return safeStringChangeBigDecimal(cell.getDateCellValue().toString());
//                } else {
//                    // 避免科学计数法
//                    double num = cell.getNumericCellValue();
//                    bigDecimal = new BigDecimal(String.valueOf(num));
//                    bigDecimal.setScale(6, RoundingMode.HALF_UP);
//
//                    return bigDecimal;
//                }
//            case BOOLEAN:
//                return bigDecimal;
//            case FORMULA:
//                try {
//                    if (evaluate) {
//                        CellValue cellValue = evaluator.evaluate(cell);
//                        // 6. 根据计算结果的数据类型进行处理
//                        switch (cellValue.getCellType()) {
//                            case NUMERIC:
//                                double numberValue = cellValue.getNumberValue();
//                                try {
//                                    bigDecimal = new BigDecimal(String.valueOf(numberValue));
//                                    bigDecimal.setScale(6, RoundingMode.HALF_UP);
//                                } catch (Exception e) {
//                                    System.out.println("cell location:" + cell.getRowIndex() + "," + cell.getColumnIndex());
//                                    e.printStackTrace();
//                                }
//
//                                return bigDecimal;
//                            case STRING:
//                                return safeStringChangeBigDecimal(cell.getStringCellValue());
//                            case BOOLEAN:
//                                return bigDecimal;
//                            case ERROR:
//                                return bigDecimal;
//                            default:
//                                return safeStringChangeBigDecimal(String.valueOf(cell.getNumericCellValue()));
//                        }
//                    } else {
//                        return safeStringChangeBigDecimal(String.valueOf(cell.getNumericCellValue()));
//                    }
//                } catch (Exception e) {
//                    try {
//                        return safeStringChangeBigDecimal(cell.getStringCellValue());
//                    } catch (Exception ex) {
//                        ex.printStackTrace();
//                        return bigDecimal;
//                    }
//                }
//            case BLANK:
//                return bigDecimal;
//            default:
//                return bigDecimal;
//        }
//    }

    /**
     * 获取列名（A, B, C, ...）
     */
    private static String getColumnName(int columnIndex) {
        StringBuilder columnName = new StringBuilder();
        while (columnIndex >= 0) {
            int remainder = columnIndex % 26;
            columnName.insert(0, (char) ('A' + remainder));
            columnIndex = (columnIndex / 26) - 1;
        }
        return columnName.toString();
    }

    /**
     * 保存JSON到文件
     */
    public void saveJsonToFile(String jsonData, String outputPath) throws IOException {
        try (FileWriter writer = new FileWriter(outputPath)) {
            writer.write(jsonData);
        }
    }

    /**
     * 获取LinkHashSet中style元素的位置
     *
     * @param set
     * @param element
     * @param <T>
     * @return
     */
    private static <T> int findPosition(Set<T> set, T element) {
        int position = 0;
        boolean found = false;
        Iterator<T> iterator = set.iterator();
        while (iterator.hasNext()) {
            T currentElement = iterator.next();
            try {
                if (currentElement.equals(element)) {
                    found = true;
                    break;
                }
            } catch (Exception e) {
                System.out.println("currentElement=============" + currentElement);
                System.out.println("-------" + element);
                throw new RuntimeException(e);
            }
            position++;
        }
        return found ? position : 0; // 如果未找到，默认0
    }

    /**
     * short颜色转换为16位格式
     */
    private static String colorConverter(short colorValue) {
        // 将short提升为int，然后转换为16进制字符串
        String hexColor = Integer.toHexString(colorValue & 0xFFFF); // 使用& 0xFFFF确保只取低16位

        // 格式化为标准的#RRGGBB格式
        // 注意：如果hexColor长度小于6，需要在前面补0
        hexColor = String.format("#%06X", colorValue & 0xFFFF);
        return hexColor;
    }

    /**
     * 解析合并单元格坐标
     */
    public static List<Map> getCellsAddress(String mergesRef) {
        String[] mergeAddrArr = StringUtils.isNotEmpty(mergesRef) ? mergesRef.split(":") : null;
        List coordinateList = null;
        if (mergeAddrArr != null && mergeAddrArr.length > 0) {
            coordinateList = new ArrayList<>();
            for (String addr : mergeAddrArr) {
                Map coordinateMap = new HashMap();
                // 使用POI内置工具类转换
                CellReference ref = new CellReference(addr);
                coordinateMap.put("row", ref.getRow());// 获取0-based行号（A6→5）
                coordinateMap.put("col", (int) ref.getCol());
                ;// 获取0-based列号（A→0）
                coordinateList.add(coordinateMap);
            }
        }
        return coordinateList;
    }

    public static List<ObjectNode> parseStrings(String[] strings) {
        List<ObjectNode> results = new ArrayList<>();

        // 正则表达式模式：匹配数字和点组成的编号，然后是名称
//            Pattern pattern = Pattern.compile("(?:.*?[:：])?((?:\\d+\\.|\\d)+\\d*)(.*)");

        for (String str : strings) {
            Matcher matcher = pattern.matcher(str);
            ObjectNode identifiName = objectMapper.createObjectNode();

            if (matcher.find()) {
                String number = matcher.group(1);
                String name = matcher.group(2).trim();

                // 处理特殊情况：编号后紧跟着数字的情况（如字符串5）
                if (!name.isEmpty() && Character.isDigit(name.charAt(0))) {
                    // 找出名称中第一个非数字非点的字符位置
                    int splitIndex = 0;
                    while (splitIndex < name.length() &&
                            (Character.isDigit(name.charAt(splitIndex)) ||
                                    name.charAt(splitIndex) == '.')) {
                        splitIndex++;
                    }

                    if (splitIndex > 0) {
                        number += name.substring(0, splitIndex);
                        name = name.substring(splitIndex).trim();
                    }
                }
                identifiName.put("number", number);
                identifiName.put("name", name);
            } else {
                // 如果没有匹配到模式，将整个字符串作为名称
                identifiName.put("number", "");
                identifiName.put("name", str);
            }
            results.add(identifiName);
        }
        return results;
    }

    /**
     * 解析SF1900 中文字母和数字  转换为  数字和中文字母
     */
    public static String swapTextAndNumber(String input, String indexDesSecCol) {
        // 匹配末尾的数字（1-4位）
        Matcher numMatcher = Pattern.compile("(\\d{1,4})$").matcher(input);
        // 匹配数字前的所有内容（中文、英文、符号等）
        Matcher textMatcher = Pattern.compile("^(.*?)(?=\\d{1,4}$)").matcher(input);

        if (numMatcher.find() && textMatcher.find()) {
            return numMatcher.group(1) + textMatcher.group(1).substring(0, textMatcher.group(1).indexOf("_"));
        } else if (!numMatcher.find() && !textMatcher.find() && !StringUtils.isEmpty(indexDesSecCol)) { //如果编号单元格为null的话，补充为前端传递的罗马数字
            return indexDesSecCol + input.substring(0, input.indexOf("_"));
        }
        return null; // 未匹配则返回null
    }

    public static String getCellReference(int row, int col) {
        Integer rowInt = Integer.valueOf(row);
        Integer colInt = Integer.valueOf(col);
        // 创建CellReference对象
        CellReference cellRef = new CellReference(rowInt, colInt);

        // 获取标准Excel格式的单元格引用
        String excelRef = cellRef.formatAsString();
        return excelRef;
    }

//        public static String expandSumFormula(String formula){
//            if (formula.startsWith("SUM(")) {
//                String range = formula.substring(4, formula.length() - 1);
//                String[] parts = range.split(":");
//                if (parts.length != 2) return formula;
//
//                try {
//                    CellReference start = new CellReference(parts[0]);
//                    CellReference end = new CellReference(parts[1]);
//                    List<String> cells = new ArrayList<>();
//
//                    for (int col = start.getCol(); col <= end.getCol(); col++) {
//                        cells.add(new CellReference(start.getRow(), col).formatAsString());
//                    }
//                    return String.join("+", cells);
//                } catch (Exception e) {
//                    return formula;
//                }
//            } else if(formula){
//
//            }
//
//            return "";
//        }


}
